package dao;

import entity.Category;
import util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CategoryDAO {
    public void add(Category category) {
        String sql = "insert into category values(null,?)";
        try(Connection c = DBUtil.getConnection();
            PreparedStatement ps = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);){

            ps.setString(1,category.name);
            ps.executeUpdate();

            ResultSet rs = ps.getGeneratedKeys();//给出自增长id，赋给config
            if(rs.next()) {
                int id = rs.getInt(1);
                category.setId(id);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void update(Category category) {
        String sql = "update category set name=? where id=?";
        try(Connection c = DBUtil.getConnection();
            PreparedStatement ps = c.prepareStatement(sql);) {

            ps.setString(1,category.name);
            ps.setInt(2,category.id);

            ps.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void delete(int id) {
        String sql = "delete from category where id = " + id;
        try(Connection c = DBUtil.getConnection();
            Statement s = c.createStatement();) {

            s.execute(sql);

        } catch (SQLException e) {
            e.printStackTrace();
        }

        if(this.list().isEmpty())
            DBUtil.truncate("category");
    }

//    public void truncate () {
//        String sql1 = "SET FOREIGN_KEY_CHECKS=0";
//        String sql2 = "SET FOREIGN_KEY_CHECKS=1";
//        String sql = "TRUNCATE TABLE category";
//        try(Connection c = DBUtil.getConnection();
//            Statement s = c.createStatement()) {
//
//            s.execute(sql1);
//            s.execute(sql);
//            s.execute(sql2);
//
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//    }

    public Category get(int id) {
        Category category = null;
        String sql = "select * from category where id = "+id;

        try(Connection c = DBUtil.getConnection();
            Statement s = c.createStatement();) {

            ResultSet rs = s.executeQuery(sql);
            if(rs.next())
            {
                category = new Category();
                String name = rs.getString("name");
                category.id = id;
                category.name = name;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return category;
    }

    public List<Category> list() {

        return list(0,Short.MAX_VALUE);
    }

    public List<Category> list(int start, int count) {
        List<Category> configList = new ArrayList<Category>();

        String sql = "select * from category order by id desc limit ?,?";

        try(Connection c = DBUtil.getConnection();
            PreparedStatement ps = c.prepareStatement(sql)) {

            ps.setInt(1,start);
            ps.setInt(2,count);

            ResultSet rs = ps.executeQuery();

            while (rs.next())
            {
                Category category = new Category();
                category.id = rs.getInt(1);
                category.name  = rs.getString(2);
                configList.add(category);
            }


        } catch (SQLException e) {
            e.printStackTrace();
        } ;

        return configList;
    }

    public int getTotal() {
        int total = 0;
        String sql = "select count(*) from category";

        try(Connection c = DBUtil.getConnection();
            PreparedStatement ps = c.prepareStatement(sql);) {

            ResultSet rs = ps.executeQuery();
//            if (rs.next()) {//这里站长用的while
//                total = rs.getInt(1);
//            }
            while (rs.next()) {
                total = rs.getInt(1);
            }

            System.out.println("total:" + total);

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return total;
    }
}
